package com.stud.common.jdbc;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.NamingCase;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.github.pagehelper.Page;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.stud.common.utils.PageUtils;
import com.stud.common.utils.reflect.ReflectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.util.*;

/**
 * @author Stud
 * @title XXXXX
 * @desription XXXXX
 * @data 2022-09-19 8:58
 */
@Slf4j
public class MyJdbcTemplate extends JdbcTemplate {

    private static void realSqlLog(SqlHelp sqlHelp) {
        String from = "";
        StackTraceElement[] stackTrace = new Exception().getStackTrace();

        for (StackTraceElement stackTraceElement : stackTrace) {
            if (!Objects.equals(stackTraceElement.getClassName(), "com.fs.common.datasource.jdbc.MyJdbcTemplate")) {
                from = stackTraceElement.getClassName() + "." + stackTraceElement.getMethodName() + ":" + stackTraceElement.getLineNumber();
                break;
            }
        }

        if (Objects.isNull(sqlHelp.getParams()) || sqlHelp.getParams().length == 0) {
            log.info("调用者:" + from + "========原始sql========\n" + sqlHelp.getSql());
            return;
        }
        String sql = sqlHelp.getSql();
        for (Object param : sqlHelp.getParams()) {
            String temp;
            if (Objects.isNull(param)) {
                temp = "null";
            } else if (param instanceof Date) {
                temp = "'" + DateUtil.formatDateTime((Date) param) + "'";
            } else if (param instanceof Number || param instanceof Boolean) {
                temp = String.valueOf(param);
            } else {
                temp = "'" + param + "'";
            }
            sql = sql.replaceFirst("[?]", temp);
        }
        log.info("调用者:" + from + "========原始sql========\n" + sql);
    }

    public <T> T find(SqlHelp sqlHelp, Class<T> clazz) {
        realSqlLog(sqlHelp);
        List<T> result = super.query(sqlHelp.getSql(), new BeanPropertyRowMapper<>(clazz), sqlHelp.getParams());
        if (CollectionUtils.isEmpty(result)) {
            return null;
        }
        return result.get(0);
    }

    /**
     * 查找单个对象 查到多条结果返回第一条
     *
     * @return Map
     */
    public Map find(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);
        List<Map<String, Object>> result = super.queryForList(sqlHelp.getSql(), sqlHelp.getParams());
        if (CollectionUtils.isEmpty(result)) {
            return null;
        }
        return result.get(0);
    }

    /**
     * 查找对象列表
     *
     * @return
     */
    public <T> List<Map<String, T>> select2Map(String sql) {
        return select2Map(SqlHelp.init(sql));
    }

    /**
     * 查找对象列表
     *
     * @return
     */
    public <T> List<Map<String, T>> select2Map(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);
        List<Map<String, T>> list = Lists.newArrayList();
        SqlRowSet sqlRowSet = super.queryForRowSet(sqlHelp.getSql(), sqlHelp.getParams());

        SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (sqlRowSet.next()) {
            Map<String, T> row = Maps.newHashMap();
            for (int i = 1; i <= columnCount; i++) {
                row.put(metaData.getColumnLabel(i), (T) sqlRowSet.getString(i));
            }
            list.add(row);
        }
        return list;
    }

    /**
     * 查找对象列表
     */
    public <T> List<T> select(String sql, Class<T> clazz) {
        return select(SqlHelp.init(sql), clazz);
    }

    /**
     * 查找对象列表
     */
    public <T> List<T> select(SqlHelp sqlHelp, Class<T> clazz) {
        realSqlLog(sqlHelp);
        String sql = sqlHelp.getSql();
        if (PageUtils.isStart()) {
            try {
                Page<T> page = new Page<>();
                sql = sql.trim();
                if (sql.endsWith(";")) {
                    sql = sql.substring(sql.length() - 1);
                }

                int total = getTotalByListSql(sqlHelp);

                List<T> result;
                if (total == 0) {
                    result = Lists.newArrayList();
                } else {
                    Page<T> localPage = PageUtils.getLocalPage();
                    if (!StringUtils.isBlank(localPage.getOrderBy())) {
                        sql += " order by " + localPage.getOrderBy();
                    }
                    sql += " limit " + (localPage.getPageNum() - 1) * localPage.getPageSize() + " , " + localPage.getPageSize();
                    result = super.query(sql, new BeanPropertyRowMapper<>(clazz), sqlHelp.getParams());
                }
                page.addAll(result);
                page.setTotal(total);
                return page;
            } finally {
                PageUtils.clearPage();
            }
        }

        return super.query(sqlHelp.getSql(), new BeanPropertyRowMapper<>(clazz), sqlHelp.getParams());
    }

    public int getTotalByListSql(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);

        String sql = sqlHelp.getSql();
        String totalSql = sql.toLowerCase();
        int start = totalSql.indexOf("select") + 6;
        int end = totalSql.indexOf("from");
        totalSql = sql.substring(0, start) + " count(*) " + sql.substring(end);
        int total = this.findSingleColumn(SqlHelp.init(totalSql, sqlHelp.getParams()), Integer.class);
        return total;
    }

    /**
     * 查找单列集合
     *
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> List<T> selectSingleColumn(SqlHelp sqlHelp, Class<T> clazz) {
        realSqlLog(sqlHelp);
        return super.query(sqlHelp.getSql(), new SingleColumnRowMapper<>(clazz), sqlHelp.getParams());
    }

    /**
     * 查找单列集合
     *
     * @return
     */
    public List<String> selectSingleColumn(SqlHelp sqlHelp) {
        return selectSingleColumn(sqlHelp, String.class);
    }

    /**
     * 查找单个字段
     */
    public String findSingleColumn(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);
        List<String> list = selectSingleColumn(sqlHelp);
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    /**
     * 查找单个字段
     */
    public <T> T findSingleColumn(SqlHelp sqlHelp, Class<T> clazz) {
        List<T> list = selectSingleColumn(sqlHelp, clazz);
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    /**
     * 查询双列,第一列作为Key,第二列作为value
     *
     * @return
     */
    public <K, V> Map<K, V> find2Map(String sql) {
        return find2Map(SqlHelp.init(sql));
    }

    /**
     * 查询双列,第一列作为Key,第二列作为value
     *
     * @return
     */
    public <K, V> Map<K, V> find2Map(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);
        Map<K, V> map = Maps.newHashMap();
        SqlRowSet sqlRowSet = super.queryForRowSet(sqlHelp.getSql(), sqlHelp.getParams());

        SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        if (columnCount == 2) {
            while (sqlRowSet.next()) {
                map.put((K) sqlRowSet.getObject(1), (V) sqlRowSet.getObject(2));
            }
        } else {
            while (sqlRowSet.next()) {
                map.put((K) sqlRowSet.getObject(1), null);
            }
        }
        return map;
    }

    public Long insert(SqlHelp sqlHelp) {
        realSqlLog(sqlHelp);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        PreparedStatementCreator psc = con -> {
            PreparedStatement ps = con.prepareStatement(sqlHelp.getSql(), PreparedStatement.RETURN_GENERATED_KEYS);
            if (!Objects.isNull(sqlHelp.getParams()) && sqlHelp.getParams().length != 0) {
                int index = 0;
                for (Object param : sqlHelp.getParams()) {
                    ps.setObject(++index, param);
                }
            }
            return ps;
        };
        super.update(psc, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    public int update(SqlHelp sqlHelp) throws DataAccessException {
        realSqlLog(sqlHelp);
        return super.update(sqlHelp.getSql(), newArgPreparedStatementSetter(sqlHelp.getParams()));
    }

    /**
     * 多个对象批量插入
     * 使用要求:
     * 要么实体必须有 @TableName 注解
     * 字段名完全按照标准的驼峰命名,或者有 @TableField 注解
     *
     * @param entities
     */
    public void insertBatch(List<?> entities) {
        if (CollectionUtils.isEmpty(entities)) {
            return;
        }

        Class<?> clazz = entities.get(0).getClass();

        TableName tableNameAnnotation = clazz.getAnnotation(TableName.class);

        if (Objects.isNull(tableNameAnnotation) || StringUtils.isBlank(tableNameAnnotation.value())) {
            throw new RuntimeException("获取表名失败");
        }

        String tableName = tableNameAnnotation.value();

        List<Field> fields = ReflectUtils.getDeclaredFields(clazz);

        StringBuilder sb = new StringBuilder();
        StringBuilder fieldSql = new StringBuilder();
        List<Object[]> batch = new ArrayList<>();

        List<Field> valueFieldList = new ArrayList<>();

        for (Field field : fields) {
            TableField tableField = field.getAnnotation(TableField.class);
            String fieldName;
            if (Objects.isNull(tableField)) {
                fieldName = NamingCase.toUnderlineCase(field.getName());
            } else {
                if (!tableField.exist()) {
                    continue;
                } else if (StringUtils.isBlank(tableField.value())) {
                    fieldName = NamingCase.toUnderlineCase(field.getName());
                } else {
                    fieldName = tableField.value();
                }
            }

            field.setAccessible(true);
            valueFieldList.add(field);

            fieldSql.append(fieldName);
            fieldSql.append(JdbcConstants.COMMA);
        }
        //这里我想保证数据按顺序入库.所以不用多线程流
        entities.forEach(entity -> {
            List<Object> tempParams = new ArrayList<>();
            valueFieldList.forEach(field -> {
                try {
                    tempParams.add(field.get(entity));
                } catch (IllegalAccessException e) {
                    log.error(e.getMessage(), e);
                    tempParams.add(null);
                }
            });
            batch.add(tempParams.toArray());
        });

        fieldSql.setLength(fieldSql.length() - 1);
        sb.append("INSERT INTO ");
        sb.append(tableName);
        sb.append(" (");
        sb.append(fieldSql);
        sb.append(")");
        sb.append(" VALUES ");
        batch.forEach(objects -> {
            sb.append(" ( ");
            for (Object object : objects) {
                if (Objects.isNull(object)) {
                    sb.append("null,");
                } else {
                    sb.append("'").append(object).append("',");
                }
            }
            sb.setLength(sb.length() - 1);
            sb.append(" ),");
        });
        sb.setLength(sb.length() - 1);

        log.info("=============================================================");
        log.info(sb.toString());
        log.info("=============================================================");

        int[] result = super.batchUpdate(sb.toString());
        log.info(JSON.toJSONString(result));
    }

}
